package com.telecom.cms.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.telecom.cms.dto.CampTargetFinishTrace;
import com.telecom.cms.util.Constant;
import com.telecom.cms.util.CurrentTime;

public class CampTargetFinishTraceDAO {
	Dao dao = new DaoImp();

	String db = (new Constant()).getPara("DATABASE");

	public int insert(CampTargetFinishTrace transientInstance) {
		int rs = 0;
		String sql = "select * from  " + db
				+ ".CAMP_TARGET_FINISH_TRACE where camp_id="
				+ transientInstance.getCampId();
		try {
			ResultSet rs2 = dao.select(sql);
			if (rs2.next())
				sql = "update " + db
						+ ".CAMP_TARGET_FINISH_TRACE set camp_id='"

						+ transientInstance.getCampId() + "',Camp_Seq_Id='"
						+ transientInstance.getCampSeqId() + "',Camp_Name='"
						+ transientInstance.getCampName() + "',Prd_Inst_Id='"
						+ transientInstance.getPrdInstId() + "',Accs_Nbr='"
						+ transientInstance.getAccsNbr()

						+ "',Camp_Group_Type_Id='"
						+ transientInstance.getCampGroupTypeId()
						+ "',User_Id='" + transientInstance.getUserId()

						+ "',Finish_State='"
						+ transientInstance.getFinishState()

						+ "',Finish_Date=timestamp'"
						+ CurrentTime.getCurrentTime()
						+ "',Cust_Inter_Channel_Id='"
						+ transientInstance.getCustInterChannelId()

						+ "',User_Contact_Nbr='"
						+ transientInstance.getUserContactNbr()

						+ "',Emp_Contact_Nbr='"
						+ transientInstance.getEmpContactNbr()
						+ "',Create_Time=timestamp'"
						+ CurrentTime.getCurrentTime()
						+ "',Adv_Serv_Time=timestamp'"
						+ CurrentTime.getCurrentTime()
						+ "',Restrict_Time=timestamp'"
						+ CurrentTime.getCurrentTime() + "',User_Name='"
						+ transientInstance.getUserName()

						+ "',Camp_Remark='" + transientInstance.getCampRemark()
						+ "',Reply_Emp_Id='"
						+ transientInstance.getReplyEmpId() + "',Reply_Name='"
						+ transientInstance.getReplyName()
						+ "',Reply_Contact_Nbr='"
						+ transientInstance.getReplyContactNbr()
						+ "',Reply_Time=timestamp'"
						+ CurrentTime.getCurrentTime() + "',Fail_Result_Id='"
						+ transientInstance.getFailResultId()

						+ "',Serv_Remark='" + transientInstance.getServRemark()
						+ "',Is_Succeed='" + transientInstance.getIsSucceed()
						+ "',Latn_Id=" + transientInstance.getLatnId()
						+ " where camp_id=" + transientInstance.getCampId()
						+ " and Camp_Seq_Id =  "
						+ transientInstance.getCampSeqId();
			else
				sql = "insert into " + db
						+ ".CAMP_TARGET_FINISH_TRACE values('"
						+ transientInstance.getCampId() + "' , '"
						+ transientInstance.getCampSeqId() + "' , '"
						+ transientInstance.getCampName() + "' , '"
						+ transientInstance.getPrdInstId() + "' , '"
						+ transientInstance.getAccsNbr() + "' , '"
						+ transientInstance.getCampGroupTypeId() + "' , '"
						+ transientInstance.getUserId() + "' , '"
						+ transientInstance.getFinishState() + "' , "
						+ "',timestamp'" + CurrentTime.getCurrentTime()
						+ "', '" + transientInstance.getCustInterChannelId()
						+ "' , '" + transientInstance.getUserContactNbr()
						+ "' , '" + transientInstance.getEmpContactNbr()
						+ "' , " + "',timestamp'"
						+ CurrentTime.getCurrentTime() + "' , "
						+ "',timestamp'" + CurrentTime.getCurrentTime()
						+ "' , " + "',timestamp'"
						+ CurrentTime.getCurrentTime() + "' , '"
						+ transientInstance.getUserName() + "' , '"
						+ transientInstance.getCampRemark() + "' , '"
						+ transientInstance.getReplyEmpId() + "' , '"
						+ transientInstance.getReplyName() + "' , '"
						+ transientInstance.getReplyContactNbr() + "' , "
						+ "',timestamp'" + CurrentTime.getCurrentTime()
						+ "' , '" + transientInstance.getFailResultId()
						+ "' , '" + transientInstance.getServRemark() + "' , '"
						+ transientInstance.getIsSucceed() + "' , '"
						+ transientInstance.getLatnId() + " ')";
			rs = dao.insert(sql);
			
			if (rs2 != null) {

				rs2.close();
			}

		} catch (Exception re) {
			System.out.println("save failed:" + re);
		}
		finally
		{
			dao.closeconn();
		}
		return rs;

	}

	public void delete(CampTargetFinishTrace persistentInstance) {

		String sql = "delete from " + db
				+ ".CAMP_TARGET_FINISH_TRACE  where camp_id="
				+ persistentInstance.getCampId() + " and Camp_Seq_Id =  "
				+ persistentInstance.getCampSeqId();

		try {
			dao.delete(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	
	public void delete(String campid,String campsegid) {

		String sql = "delete from " + db
				+ ".CAMP_TARGET_FINISH_TRACE  where camp_id="
				+ campid + " and Camp_Seq_Id =  "
				+ campsegid;
		try {
			dao.delete(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	
	public void insertfromappd(String campid,String campsegid) {

		String sql = "INSERT INTO  " + db+".CAMP_TARGET_FINISH_TRACE "
				+"("
				+"Camp_Id"
				+",Camp_Seq_Id"
				+",Cust_Id"
				+",Accs_Nbr"
				+",User_Id"
				+",User_Contact_Nbr"
				+",Latn_Id"
				+") "
				+"SELECT P1.Camp_Id "
				+"      ,P1.Camp_Seq_Id "
				+"      ,P1.Cust_Id"
				+"      ,P1.Accs_Nbr"
				+"      ,P1.User_Id"
				+"      ,P1.Accs_Nbr     "
				+"      ,P1.Latn_Id"
				+" FROM  " + db+".CAMP_CUST_APPD_RSLT P1 "
				+" where camp_id="
				+ campid + " and Camp_Seq_Id =  "
				+ campsegid;
		try {
			dao.insert(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public CampTargetFinishTrace findById(int id1, int id2) {

		String sql = "select * from " + db
				+ ".CAMP_TARGET_FINISH_TRACE where camp_id=" + id1
				+ " and Camp_Seq_Id =  " + id2;
		CampTargetFinishTrace sd = new CampTargetFinishTrace();
		try {
			ResultSet rs = dao.select(sql);
			if (rs.next()) {
				sd.setCampId(rs.getInt(1));
				sd.setCampSeqId(rs.getInt(2));
				sd.setCampName(rs.getString(3));

				sd.setPrdInstId(rs.getString(4));

				sd.setAccsNbr(rs.getString(5));

				sd.setCampGroupTypeId(rs.getString(6));

				sd.setUserId(rs.getInt(7));

				sd.setFinishState(rs.getString(8));

				sd.setFinishDate(rs.getString(9));

				sd.setCustInterChannelId(rs.getString(10));

				sd.setUserContactNbr(rs.getString(11));

				sd.setEmpContactNbr(rs.getString(12));

				sd.setCreateTime(rs.getString(13));

				sd.setAdvServTime(rs.getString(14));

				sd.setRestrictTime(rs.getString(15));

				sd.setUserName(rs.getString(16));

				sd.setCampRemark(rs.getString(17));
				sd.setReplyEmpId(rs.getString(18));
				sd.setReplyName(rs.getString(19));
				sd.setReplyContactNbr(rs.getString(20));
				sd.setReplyTime(rs.getString(21));
				sd.setFailResultId(rs.getString(22));
				sd.setServRemark(rs.getString(23));
				sd.setIsSucceed(rs.getString(24));
				sd.setLatnId(rs.getString(25));

			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return sd;

	}

	public List findAll() {

		String sql = "select * from " + db + ".CAMP_TARGET_FINISH_TRACE";

		List list = new ArrayList();
		try {
			ResultSet rs = dao.select(sql);
			while (rs.next()) {
				CampTargetFinishTrace sd = new CampTargetFinishTrace();
				sd.setCampId(rs.getInt(1));
				sd.setCampSeqId(rs.getInt(2));
				sd.setCampName(rs.getString(3));

				sd.setPrdInstId(rs.getString(4));

				sd.setAccsNbr(rs.getString(5));

				sd.setCampGroupTypeId(rs.getString(6));

				sd.setUserId(rs.getInt(7));

				sd.setFinishState(rs.getString(8));

				sd.setFinishDate(rs.getString(9));

				sd.setCustInterChannelId(rs.getString(10));

				sd.setUserContactNbr(rs.getString(11));

				sd.setEmpContactNbr(rs.getString(12));

				sd.setCreateTime(rs.getString(13));

				sd.setAdvServTime(rs.getString(14));

				sd.setRestrictTime(rs.getString(15));

				sd.setUserName(rs.getString(16));

				sd.setCampRemark(rs.getString(17));
				sd.setReplyEmpId(rs.getString(18));
				sd.setReplyName(rs.getString(19));
				sd.setReplyContactNbr(rs.getString(20));
				sd.setReplyTime(rs.getString(21));
				sd.setFailResultId(rs.getString(22));
				sd.setServRemark(rs.getString(23));
				sd.setIsSucceed(rs.getString(24));
				sd.setLatnId(rs.getString(25));
				list.add(sd);
			}
			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dao.closeconn();
		}
		return list;

	}

}